﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using FisheryPlatform.Entity;
using FisheryPlatform.DataAccess.DBUtility;

namespace FisheryPlatform.DataAccess.Account
{
    public class UsersDB
    {
        #region private const
        private const string PARAM_USER_ID = "@UserId";
        private const string PARAM_LOGIN_NAME = "@LoginName";
        private const string PARAM_LOWERED_LOGIN_NAME = "@LoweredLoginName";
        private const string PARAM_PASSWORD = "@Password";
        private const string PARAM_E_MAIL = "@EMail";
        private const string PARAM_REAL_NAME = "@RealName";
        private const string PARAM_MOBILE = "@Mobile";
        private const string PARAM_PHONE = "@Phone";
        private const string PARAM_STATUS = "@Status";
        private const string PARAM_UPDATE_DATE = "@UpdateDate";
        private const string PARAM_CREATE_DATE = "@CreateDate";

        private static readonly string ConnectionString = SQLHelper.CONNECTION_STRING;
        #endregion

        private static void FillEntityFrom(SqlDataReader reader, UsersEntity obj)
        {
            #region
            if (reader != null && !reader.IsClosed)
            {
                obj.UserId = reader.IsDBNull(reader.GetOrdinal("UserId")) ? 0 : reader.GetInt64(reader.GetOrdinal("UserId"));
                obj.LoginName = reader.IsDBNull(reader.GetOrdinal("LoginName")) ? String.Empty : reader.GetString(reader.GetOrdinal("LoginName"));
                obj.LoweredLoginName = reader.IsDBNull(reader.GetOrdinal("LoweredLoginName")) ? String.Empty : reader.GetString(reader.GetOrdinal("LoweredLoginName"));
                obj.Password = reader.IsDBNull(reader.GetOrdinal("Password")) ? String.Empty : reader.GetString(reader.GetOrdinal("Password"));
                obj.EMail = reader.IsDBNull(reader.GetOrdinal("EMail")) ? String.Empty : reader.GetString(reader.GetOrdinal("EMail"));
                obj.RealName = reader.IsDBNull(reader.GetOrdinal("RealName")) ? String.Empty : reader.GetString(reader.GetOrdinal("RealName"));
                obj.Mobile = reader.IsDBNull(reader.GetOrdinal("Mobile")) ? String.Empty : reader.GetString(reader.GetOrdinal("Mobile"));
                obj.Phone = reader.IsDBNull(reader.GetOrdinal("Phone")) ? String.Empty : reader.GetString(reader.GetOrdinal("Phone"));
                obj.Status = reader.IsDBNull(reader.GetOrdinal("Status")) ? Convert.ToByte(0) : reader.GetByte(reader.GetOrdinal("Status"));
                obj.UpdateDate = reader.IsDBNull(reader.GetOrdinal("UpdateDate")) ? DateTime.MinValue : reader.GetDateTime(reader.GetOrdinal("UpdateDate"));
                obj.CreateDate = reader.IsDBNull(reader.GetOrdinal("CreateDate")) ? DateTime.MinValue : reader.GetDateTime(reader.GetOrdinal("CreateDate"));
            }
            #endregion
        }

        private static SqlParameter[] GetUsersParams(UsersEntity obj)
        {
            #region
            SqlParameter[] dbParams ={					
					 SQLHelper.MakeParam(PARAM_USER_ID, SqlDbType.BigInt,0,obj.UserId),
					 SQLHelper.MakeParam(PARAM_LOGIN_NAME, SqlDbType.NVarChar, 255,obj.LoginName),					
					 SQLHelper.MakeParam(PARAM_PASSWORD, SqlDbType.VarChar, 50,obj.Password),					
					 SQLHelper.MakeParam(PARAM_E_MAIL, SqlDbType.NVarChar, 255,obj.EMail),					
					 SQLHelper.MakeParam(PARAM_REAL_NAME, SqlDbType.NVarChar, 50,obj.RealName),					
					 SQLHelper.MakeParam(PARAM_MOBILE, SqlDbType.NVarChar, 100,obj.Mobile),					
					 SQLHelper.MakeParam(PARAM_PHONE, SqlDbType.NVarChar, 100,obj.Phone),					
					 SQLHelper.MakeParam(PARAM_STATUS, SqlDbType.TinyInt,0,obj.Status)
			};

            return dbParams;
            #endregion
        }

        public static bool Save(UsersEntity obj)
        {
            #region
            try
            {
                SqlParameter[] dbParams = GetUsersParams(obj);

                if (obj.UserId == 0)
                {
                    obj.UserId = Convert.ToInt32(SQLHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Users_InsertUpdate", dbParams));
                    return obj.UserId > 0 ? true : false;
                }
                else
                {
                    var affectNum = SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Users_InsertUpdate", dbParams);
                    return affectNum > 0 ? true : false;
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Users-->InsertOrUpdate-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }

            return false;
            #endregion
        }

        public static List<UsersEntity> GetListByUserIds(string userIds)
        {
            #region
            userIds = userIds.TrimEnd(',');
            if (string.IsNullOrEmpty(userIds))
            {
                return new List<UsersEntity>();
            }
            var pageCount=0;
            return Search(" UserId in(" + userIds + ")", 0, 0, out pageCount);
            #endregion
        }

        public static List<UsersEntity> Search(string sqlWhere, int pageIndex, int pageSize, out int pageCount)
        {
            #region
            List<UsersEntity> result = new List<UsersEntity>();
            pageCount = 0;
            try
            {
                if (string.IsNullOrEmpty(sqlWhere))
                {
                    sqlWhere = " 1=1 ";
                }

                SqlParameter[] dbParams =
                {
                    SQLHelper.MakeParam("@sqlWhere", SqlDbType.NVarChar,0,sqlWhere),
                    SQLHelper.MakeParam("@pageIndex",SqlDbType.Int,0,pageIndex),
                    SQLHelper.MakeParam("@pageSize",SqlDbType.Int,0,pageSize),
                    SQLHelper.MakeOutParam("@pageCount", SqlDbType.Int,0,0)                    
                };
                using (SqlDataReader reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Users_Search", dbParams))
                {
                    while (reader.Read())
                    {
                        UsersEntity model = new UsersEntity();
                        FillEntityFrom(reader, model);
                        result.Add(model);
                    }
                }
                pageCount = int.Parse(dbParams[3].Value.ToString());
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Users-->Search-->" + e.ToString());
                ExceptionMessageDB.Record(ex);
            }
            return result;
            #endregion
        }

        public static bool DeleteByUserId(long userId)
        {
            #region
            try
            {
                SqlParameter[] dbParams = new SqlParameter[] { SQLHelper.MakeParam(PARAM_USER_ID, SqlDbType.BigInt, 0, userId) };

                return SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Users_DeleteByUserId", dbParams) > 0 ? true : false;

            }

            catch (Exception e)
            {
                Exception ex = new Exception("Users-->DeleteByUserId-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }

            return false;
            #endregion
        }

        public static UsersEntity GetByUserId(long userId)
        {
            #region
            SqlDataReader reader = null;
            UsersEntity obj = new UsersEntity();

            try
            {
                SqlParameter[] dbParams = new SqlParameter[] { SQLHelper.MakeParam(PARAM_USER_ID, SqlDbType.BigInt, 0, userId) };

                reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Users_GetByUserId", dbParams);

                if (reader.Read())
                {
                    FillEntityFrom(reader, obj);
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Users-->GetByUserId-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }

            return obj;
            #endregion
        }

        public static UsersEntity GetUserInfo(string loginName, string password)
        {
            #region
            SqlDataReader reader = null;
            UsersEntity obj = new UsersEntity();
            try
            {
                SqlParameter[] dbParams = new SqlParameter[] { 
                        SQLHelper.MakeParam(PARAM_LOGIN_NAME, SqlDbType.NVarChar, 255, loginName.Trim()),
                        SQLHelper.MakeParam(PARAM_PASSWORD, SqlDbType.VarChar, 50, password.Trim())
                };

                reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Users_GetByLoginNameAndPassword", dbParams);

                if (reader.Read())
                {
                    FillEntityFrom(reader, obj);
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Users-->GetUserInfo-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }
            return obj;
            #endregion
        }

        public static bool UpdatePassword(long userId, string password)
        {
            #region
            try
            {
                SqlParameter[] dbParams = new SqlParameter[] { 
                    SQLHelper.MakeParam(PARAM_USER_ID, SqlDbType.BigInt, 0, userId),
                    SQLHelper.MakeParam(PARAM_PASSWORD, SqlDbType.VarChar, 0,password)
                };

                return SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Users_UpdatePassword", dbParams) > 0 ? true : false;

            }

            catch (Exception e)
            {
                Exception ex = new Exception("Users-->UpdatePassword-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }

            return false;
            #endregion
        }
    }
}
